/* ============ DATABASE ============ */
USE MASTER
GO

IF DB_ID("ServicesDirectory") IS NOT NULL
DROP DATABASE ServicesDirectory
GO

CREATE DATABASE ServicesDirectory
GO

USE ServicesDirectory
GO

/* ============ TABLES ============ */
CREATE TABLE Countries(
	CountryId				varchar(20)			NOT NULL,
	CountryName				nvarchar(50)		NOT NULL,
	CONSTRAINT PK_Countries			primary key(CountryId)
)
GO

CREATE TABLE Counties(
	CountyId				varchar(20)			NOT NULL,
	CountyName				varchar(50)			NOT NULL,
	CountryId				varchar(20)			NOT NULL,
    CONSTRAINT	PK_Counties				primary key(CountyId),
	CONSTRAINT  FK_Counties_CountryId	foreign key(CountryId) references Countries(CountryId)
)

CREATE TABLE GovOfficeRegions(
	GovOfficeRegionId		int identity(1,1)	NOT NULL,
	GovOfficeRegionName		nvarchar(50)		NOT NULL,
	[Description]			nvarchar(256)		NULL,
	CountyId				varchar(20)			NOT NULL,
	IsActive				bit					NOT NULL DEFAULT (1),
	CONSTRAINT PK_GovOfficeRegions		primary key(GovOfficeRegionId),
	CONSTRAINT FK_GovOfficeRegions_CountyId
							foreign key(CountyId) references Counties(CountyId)
)
GO


